﻿using System;
using System.Data;
using System.Transactions;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;
using Skyworth.CRM.Common;

namespace Skyworth.CRM.DAL
{
    public class CustBussinessoppDAL : SuperDataBase, ICustBussinessoppDAL
    {
        public string Add(CustBussinessopp model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select @NewCode=case isnull(MAX(FNumber),'1') when '1' then 'Bus' + CONVERT(varchar(100),GETDATE(), 112) + '0001'");
                strSql.Append(" else 'Bus' + CAST(cast(right(MAX(FNumber),12) as bigint)+1 as varchar) end from T_Cust_Bussinessopp");
                strSql.Append(" where LEFT(FNumber,11) = 'Bus' + CONVERT(varchar(100),GETDATE(), 112);");
                strSql.Append("insert into T_Cust_Bussinessopp(");
                strSql.Append("FDate,FNumber,FMaker,FComName,FStrength,FAddress,FEmpId,FArea,FChannel,FProducts,FWebsite,FNote)");
                strSql.Append(" values (");
                strSql.Append("@FDate,@NewCode,@FMaker,@FComName,@FStrength,@FAddress,@FEmpId,@FArea,@FChannel,@FProducts,@FWebsite,@FNote)");
                //strSql.Append(";select @@IDENTITY");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FDate", DbType.DateTime, model.FDate);
                //DB.AddInParameter(cmd, "@FNumber", DbType.String, model.FNumber);
                DB.AddInParameter(cmd, "@FMaker", DbType.Int32, model.FMaker);
                DB.AddInParameter(cmd, "@FComName", DbType.String, model.FComName);
                DB.AddInParameter(cmd, "@FStrength", DbType.String, model.FStrength);
                DB.AddInParameter(cmd, "@FAddress", DbType.String, model.FAddress);
                DB.AddInParameter(cmd, "@FEmpId", DbType.Int32, model.FEmpId);
                DB.AddInParameter(cmd, "@FArea", DbType.String, model.FArea);
                DB.AddInParameter(cmd, "@FChannel", DbType.String, model.FChannel);
                DB.AddInParameter(cmd, "@FProducts", DbType.String, model.FProducts);
                DB.AddInParameter(cmd, "@FWebsite", DbType.String, model.FWebsite);
                DB.AddInParameter(cmd, "@FNote", DbType.String, model.FNote);
                DB.AddOutParameter(cmd, "@NewCode", DbType.String, 50);
                DB.ExecuteNonQuery(cmd);
                string code = DB.GetParameterValue(cmd, "@NewCode").ToString();
                return code;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(CustBussinessopp model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update T_Cust_Bussinessopp set ");
                strSql.Append("FDate=@FDate,");
                //strSql.Append("FNumber=@FNumber,");
                strSql.Append("FMaker=@FMaker,");
                strSql.Append("FComName=@FComName,");
                strSql.Append("FStrength=@FStrength,");
                strSql.Append("FAddress=@FAddress,");
                strSql.Append("FEmpId=@FEmpId,");
                strSql.Append("FArea=@FArea,");
                strSql.Append("FChannel=@FChannel,");
                strSql.Append("FProducts=@FProducts,");
                strSql.Append("FWebsite=@FWebsite,");
                strSql.Append("FNote=@FNote");
                strSql.Append(" where FItemId=@FItemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FItemId", DbType.Int32, model.FItemId);
                DB.AddInParameter(cmd, "@FDate", DbType.DateTime, model.FDate);
                //DB.AddInParameter(cmd, "@FNumber", DbType.String, model.FNumber);
                DB.AddInParameter(cmd, "@FMaker", DbType.Int32, model.FMaker);
                DB.AddInParameter(cmd, "@FComName", DbType.String, model.FComName);
                DB.AddInParameter(cmd, "@FStrength", DbType.String, model.FStrength);
                DB.AddInParameter(cmd, "@FAddress", DbType.String, model.FAddress);
                DB.AddInParameter(cmd, "@FEmpId", DbType.Int32, model.FEmpId);
                DB.AddInParameter(cmd, "@FArea", DbType.String, model.FArea);
                DB.AddInParameter(cmd, "@FChannel", DbType.String, model.FChannel);
                DB.AddInParameter(cmd, "@FProducts", DbType.String, model.FProducts);
                DB.AddInParameter(cmd, "@FWebsite", DbType.String, model.FWebsite);
                DB.AddInParameter(cmd, "@FNote", DbType.String, model.FNote);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete(int FItemId)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from T_Cust_Bussinessopp ");
                strSql.Append(" where FItemId=@FItemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FItemId", DbType.Int32, FItemId);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public CustBussinessopp GetModel(int fItemId)
        {
            try
            {
                CustBussinessopp model = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  top 1 FItemId,FDate,FNumber,FMaker,FComName,FStrength,FAddress,FEmpId,FArea,FChannel,FProducts,FWebsite,FNote from T_Cust_Bussinessopp ");
                strSql.Append(" where FItemId=@FItemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FItemId", DbType.Int32, fItemId);
                using (IDataReader dr = DB.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        model = new CustBussinessopp();
                        if (dr["FItemId"].ToString() != "")
                        {
                            model.FItemId = int.Parse(dr["FItemId"].ToString());
                        }
                        if (dr["FDate"].ToString() != "")
                        {
                            model.FDate = DateTime.Parse(dr["FDate"].ToString());
                        }
                        model.FNumber = dr["FNumber"].ToString();
                        if (dr["FMaker"].ToString() != "")
                        {
                            model.FMaker = int.Parse(dr["FMaker"].ToString());
                        }
                        model.FComName = dr["FComName"].ToString();
                        model.FStrength = dr["FStrength"].ToString();
                        model.FAddress = dr["FAddress"].ToString();
                        if (dr["FEmpId"].ToString() != "")
                        {
                            model.FEmpId = int.Parse(dr["FEmpId"].ToString());
                        }
                        model.FArea = dr["FArea"].ToString();
                        model.FChannel = dr["FChannel"].ToString();
                        model.FProducts = dr["FProducts"].ToString();
                        model.FWebsite = dr["FWebsite"].ToString();
                        model.FNote = dr["FNote"].ToString();
                    }
                }
                return model;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Query(string strWhere)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"select FItemId,FDate,FNumber,FMaker,tbe1.EmpName as FMakerName,FComName,FArea,FStrength,FAddress,
FEmpId,tbe2.EmpName,FChannel,FProducts,FWebsite,FNote
FROM T_Cust_Bussinessopp as tcb left join T_BasicData_Employee as tbe1 on tbe1.EmpId=tcb.FMaker
left join T_BasicData_Employee as tbe2 on tcb.FEmpId=tbe2.EmpId where 1=1");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(strWhere);
                }
                DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString());
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 分页获得数据列表
        /// </summary>
        public DataTable Query(string strWhere, string orderKey, int pageSize, int pageIndex, out int recordCount)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"select FItemId,FDate,FNumber,FMaker,tbe1.EmpName as FMakerName,FComName,FArea,FStrength,FAddress,
FEmpId,tbe2.EmpName,FChannel,FProducts,FWebsite,FNote
FROM T_Cust_Bussinessopp as tcb left join T_BasicData_Employee as tbe1 on tbe1.EmpId=tcb.FMaker
left join T_BasicData_Employee as tbe2 on tcb.FEmpId=tbe2.EmpId where 1=1");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(strWhere);
                }
                string countsql = "select count(1) from (" + strSql.ToString() + ") as FCount";
                //计算开始索引和结束索引
                int startIndex = pageSize * (pageIndex - 1) + 1;
                int endIndex = startIndex + pageSize - 1;
                //调用分页Sql方法
                string pageSql = Pagination.PageSql(strSql.ToString(), orderKey, startIndex, endIndex);
                //记录总数
                DbCommand cmd = DB.GetSqlStringCommand(countsql);
                recordCount = Convert.ToInt32(DB.ExecuteScalar(cmd));
                cmd = DB.GetSqlStringCommand(pageSql);
                return DB.ExecuteDataSet(cmd).Tables[0];
            }
            catch (Exception ex)
            {                
                throw ex;
            }            
        }
    }
}
